import re
from io import BytesIO
from urllib.parse import quote

import MySQLdb
from MySQLdb.cursors import DictCursor
from django.core.paginator import Paginator
from django.db.models import Q
from django.db.transaction import get_connection
from django.http import JsonResponse, HttpResponse
from django.shortcuts import render, redirect

# Create your views here.
from openpyxl import Workbook

from app.models import Record, Car


def index(request):
    if request.method == 'POST':
        carno = request.POST.get('car_id')
        start = request.POST.get('start')
        end = request.POST.get('end')
        return redirect(f'show/?carno={carno}&start={start}&end={end}')
    return render(request, 'index.html')


def show_data(request):
    carno = request.GET.get('carno')
    start = request.GET.get('start')
    end = request.GET.get('end')
    res = []
    # dic = {}

    # zh_pattern = re.compile(r'^[京津沪渝冀豫云辽黑湘皖鲁新苏浙赣鄂桂甘晋蒙陕吉闽贵粤青藏川宁琼使领A-Z]{1}[A-Z]{1}')
    # if re.match(zh_pattern, carno):
    #     cars = Car.objects.filter(carno__icontains=carno)
    # else:
    #     cars = Car.objects.filter(owner__icontains=carno)

    # cars = Car.objects.filter(Q(carno__icontains=carno) | Q(owner__icontains=carno))
    # for car in cars:
    #     if start and end:
    #         records = Record.objects.filter(car_id=car.id).filter(makedate__range=[start, end])
    #     else:
    #         records = Record.objects.filter(car_id=car.id)
    #     for record in records:
    #         one_car = {'carno': car.carno, 'owner': car.owner, 'reason': record.reason,
    #                    'makedate': record.makedate, 'punish': record.punish,
    #                    'dealt': record.dealt, 'id': record.id}
    #         res.append(one_car)

    # 外键使用
    records = Record.objects.all().select_related('car')
    if carno:
        records = records.filter(Q(car__owner__icontains=carno) | Q(car__carno__icontains=carno))
    if start and end:
        records = records.filter(makedate__range=[start, end])
    res = records
    paginator = Paginator(res, 5)
    page_num = request.GET.get('page', 1)
    page_obj = paginator.get_page(page_num)

    return render(request, 'index.html', locals())


def del_record(request):
    dealt = request.GET.get('dealt')
    id = request.GET.get('record')
    if dealt == 'True':
        msg = '请先进行受理'
        code = 10001
    if dealt == 'False' and id:
        record = Record.objects.filter(id=id).first()
        record.delete()
        msg = '删除成功'
        code = 10000
    return JsonResponse({'code': code, 'msg': msg})


def dealt(request):
    if request.GET.get('dealt') == 'True':
        record = Record.objects.filter(id=request.GET.get('record')).first()
        record.dealt = 0
        record.save()
        msg = '受理成功'
        code = 10000
    return JsonResponse({'msg': msg, 'code': code})


def export(request):
    carno = request.GET.get('carno', '')
    print(carno)
    start = request.GET.get('start')
    end = request.GET.get('end')
    records = Record.objects.all().select_related('car')
    if carno:
        records = records.filter(Q(car__owner__icontains=carno) | Q(car__carno__icontains=carno))
    if start and end:
        records = records.filter(makedate__range=[start, end])
    wb = Workbook()
    ws = wb.active
    col_name = ('车牌号', '车主姓名', '违章原因', '违章时间', '出发方式', '是否受理')
    for index, col in enumerate('ABCDEF'):
        ws[f'{col}1'] = col_name[index]
    row = 2
    for record in records:
        ws[f'A{row}'] = record.car.carno
        ws[f'B{row}'] = record.car.owner
        ws[f'C{row}'] = record.reason
        ws[f'D{row}'] = record.makedate
        ws[f'E{row}'] = record.punish
        ws[f'F{row}'] = '已受理' if record.dealt else '未受理'
        row += 1
    buffer = BytesIO()
    wb.save(buffer)
    filename = quote('违章记录.xlsx')
    resp = HttpResponse(buffer.getvalue(),
                        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
                        )
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp


def make_char(request):
    datax = []
    datay = []
    msg = '绘图失败'
    code = 10001
    carno = request.GET.get('carno', ' ')
    print(carno)
    conn = get_connection()
    if conn:
        with conn.cursor() as cursor:
            cursor.execute(
                'select year(makedate) as ry, count(id) as total from tb_record group by ry'
            )
            for row in cursor.fetchall():
                datax.append(row[0])
                datay.append(row[1])
        msg = 'ok'
        code = 10000
    res = {
        'code': code,
        'msg': msg,
        'datax': datax,
        'datay': datay,
    }
    return JsonResponse(res)
